package com.whsxt.dao;

import com.whsxt.common.PageInfo;
import com.whsxt.util.JdbcUtil;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 能处理所有数据表的基本的增删改查 分页查询的功能
 */
public abstract class BaseDao {
    /**
     * 处理添加并回显示ID  选择性掌握
     */
    protected Object addWithAutoId(String sql, Object obj, Object[] args, String primaryKey) {
        //得到连接
        Connection connection = JdbcUtil.getConn();
        //从连接里面得到预编译对象
        PreparedStatement pstmt = null;
        //声明ResultSet
        ResultSet rs = null;
        try {
            pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            //设置占位符的参数
            if (null != args && args.length > 0) {
                for (int i = 0; i < args.length; i++) {
                    pstmt.setObject((i + 1), args[i]);
                }
            }
            //执行SQL
            int i = pstmt.executeUpdate();
            if (i > 0) {
                rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    Class<?> cls = obj.getClass();
                    Field primaryKeyField = cls.getDeclaredField(primaryKey);
                    primaryKeyField.setAccessible(true);
                    Object object = rs.getObject(1);
                    //取出从数据库里面取出来的object的类型
                    if (object instanceof Long) {
                        if (primaryKeyField.getType().getSimpleName().equals("Integer")) {
                            primaryKeyField.set(obj, Integer.valueOf(object.toString()));
                        } else if (primaryKeyField.getType().getSimpleName().equals("Long")) {
                            primaryKeyField.set(obj, object);
                        }
                    } else if (object instanceof Integer) {
                        if (primaryKeyField.getType().getSimpleName().equals("Integer")) {
                            primaryKeyField.set(obj, object);
                        } else if (primaryKeyField.getType().getSimpleName().equals("Long")) {
                            primaryKeyField.set(obj, Long.valueOf(object.toString()));
                        }
                    }
                }
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, pstmt, rs);
        }
        //关闭
        return obj;
    }

    /**
     * 公共的添加 修改  删除的方法
     *
     * @param sql
     * @param args
     * @return
     */
    protected Integer executeUpdate(String sql, Object... args) {
        Integer index = 0;
        //得到连接
        Connection connection = JdbcUtil.getConn();
        //从连接里面得到预编译对象
        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sql);
            //设置占位符的参数
            if (null != args && args.length > 0) {
                for (int i = 0; i < args.length; i++) {
                    pstmt.setObject((i + 1), args[i]);
                }
            }
            //执行SQL
            index = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, pstmt);
        }

        return index;
    }

    /**
     * 处理全查询
     */
    protected <T> List<T> selectList(String sql, Class<T> cls, Object... args) {
        List<T> dataList = new ArrayList<>();//返回的集合
        //得到连接
        Connection connection = JdbcUtil.getConn();
        //创建预编译对象
        PreparedStatement pstmt = null;
        //声明一个结果集
        ResultSet rs = null;
        try {
            pstmt = connection.prepareStatement(sql);
            //处理可能存在的占位符
            if (null != args && args.length > 0) {
                for (int i = 0; i < args.length; i++) {
                    pstmt.setObject((i + 1), args[i]);
                }
            }
            //执行SQL
            rs = pstmt.executeQuery();
            //提取元数据
            ResultSetMetaData metaData = rs.getMetaData();
            //取出列的总个数
            int columnCount = metaData.getColumnCount();
            //处理结果
            while (rs.next()) {
                //创建Class的对象
                T t = cls.newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    //得到sql执行完成之后的列名
                    String columnLabel = metaData.getColumnLabel(i);
                    //得到columnLabel对应的值
                    Object value = rs.getObject(columnLabel);
                    //根据columnLabel通过反射得到t里面的columnLabel对应的属性对象
                    Field field = cls.getDeclaredField(columnLabel);
                    //暴力破解
                    field.setAccessible(true);
                    //把value放到t对象的columnLabel对应的属性上面
                    field.set(t, value);
                }
                //向返回的dataList里面添加数据
                dataList.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, pstmt, rs);
        }

        return dataList;
    }

    /**
     * 处理查询一个
     */
    protected <T> T selectOne(String sql, Class<T> cls, Object... args) {
        List<T> selectList = this.selectList(sql, cls, args);
        if (null != selectList && !selectList.isEmpty()) {
            return selectList.get(0);
        }
        return null;
    }

    /**
     * 分页查询
     * @param sql
     * @param clss
     * @param page
     * @param limit
     * @param <T>
     * @return
     */
    protected <T> PageInfo<T> selectPage(String sql, Class<T> clss, String page, String limit) {
        //查询总页数
        PageInfo<T> pageInfo = new PageInfo<>();
        Integer pageNo = Integer.parseInt(page);
        Integer pageSize = Integer.parseInt(limit);
        pageInfo.setPage(pageNo);
        pageInfo.setLimit(pageSize);
        //pageInfo.setTotal();//符合查询条件的总数据条数
        Integer count = selectCount(sql);
        pageInfo.setTotal(count);
        if (count == 0){//说明没有数据
            pageInfo.setData(new ArrayList<T>());
            return  pageInfo;
        }
        //limit
        String dataSql = sql + " limit " + (pageNo - 1) * pageSize +","+pageSize;
        List<T> data = this.selectList(dataSql, clss);
        pageInfo.setData(data);
        return  pageInfo;
    }

    /**
     * 查询符合条件数据条数
     * @param sql
     * @return
     */
    protected Integer selectCount(String sql) {
        String countSql = "select count(1) from  ";
        sql = sql.toUpperCase();
        int fromIndex = sql.indexOf("FROM");
        sql = sql.substring(fromIndex+4);
        countSql = countSql  + sql;
        PreparedStatement prep = null;
        ResultSet rs = null;
        Connection conn = null;
        try{
            conn = JdbcUtil.getConn();
            prep = conn.prepareStatement(countSql);
            rs = prep.executeQuery();
            rs.next();
            int total = rs.getInt(1);
            return total;

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn,prep,rs);
        }
        return  0;
    }


}
